package com.bbs.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.bbs.entity.Article;
import com.bbs.entity.Blocks;
import com.bbs.entity.Collection;
import com.bbs.entity.User;
import com.bbs.util.DBHelp;
import com.bbs.util.Page;
import com.bbs.util.RowMapper;

public class CollectionDao {
	private DBHelp<Collection> dh = new DBHelp<Collection>();
	
	public void deleteByAid(int id){
		String sql = "DELETE FROM t_collection WHERE t_article_id = ?";
		dh.update(sql, id);
	}
	
	public void save(Collection col) {
		String sql = "INSERT INTO t_collection (t_user_id, t_article_id, createdate) VALUE(?,?,?)";
		dh.update(sql, col.getUid(), col.getAid(), col.getCreatedate());
	}

	public List<Collection> findByuid(int id, Page p) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT c.id,c.t_user_id,c.t_article_id,c.createdate,cu.id AS cuserid,cu.username AS cusername,");
		sql.append("cu.userpwd AS cuserpwd,cu.score AS cuscore,cu.level AS culevel,cu.headpic AS cuheadpic,");
		sql.append("cu.createdate AS cusercreatedate,cu.email AS cuemail,cu.role,cu.state as custate,cu.active as cuactive,a.id AS articleid,");
		sql.append("a.name,a.content,a.createdate AS articlecreatedate,a.t_user_id,a.state AS articlestate,a.t_blocks_id,");
		sql.append("a.clicknum,a.type,a.settoptime,au.id AS auserid,au.username AS ausername,au.userpwd AS auserpwd,");
		sql.append("au.score AS auscore,au.level AS aulevel,au.headpic AS auheadpic,au.createdate AS aucreatedate,");
		sql.append("au.email AS auemail,au.role AS aurole,au.state AS austate,au.active AS auactive,b.id AS blocksid,");
		sql.append("b.name AS blocksname,b.pid,b.content AS blockscontent FROM t_collection AS c LEFT JOIN t_user AS cu ");
		sql.append("ON c.t_user_id = cu.id LEFT JOIN t_article AS a ON c.t_article_id = a.id LEFT JOIN t_user AS au ");
		sql.append("ON a.t_user_id = au.id LEFT JOIN t_blocks AS b ON a.t_blocks_id = b.id WHERE cu.id = ? LIMIT ?,?");
		return dh.queryForList(sql.toString(), new CollectionMapper(), id,p.getStartNum(),p.getPageSize());
	}

	public int getCountByuid(int id) {
		String sql = "SELECT COUNT(*) FROM t_collection WHERE t_user_id = ?";
		return dh.queryForCount(sql, id);
	}
}


class CollectionMapper implements RowMapper<Collection> {
	public Collection maprow(ResultSet rs) throws SQLException {
		Collection collection = new Collection();
		collection.setId(rs.getInt("id"));
		collection.setCreatedate(rs.getString("createdate"));
		
		User cu = new User();
		cu.setActive(rs.getInt("cuactive"));
		cu.setCreatedate(rs.getString("cusercreatedate"));
		cu.setEmail(rs.getString("cuemail"));
		cu.setHeadpic(rs.getString("cuheadpic"));
		cu.setId(rs.getInt("cuserid"));
		cu.setLevel(rs.getInt("culevel"));
		cu.setRole(rs.getInt("curole"));
		cu.setScore(rs.getInt("cuscore"));
		cu.setState(rs.getBoolean("custate"));
		cu.setUsername(rs.getString("cusername"));
		cu.setUserpwd(rs.getString("cuserpwd"));
		collection.setUser(cu);
		
		Article a = new Article();
		a.setClickNum(rs.getInt("clicknum"));
		a.setContent(rs.getString("content"));
		a.setCreatedate(rs.getString("articlecreatedate"));
		a.setId(rs.getInt("articleid"));
		a.setName(rs.getString("name"));
		a.setSettoptime(rs.getString("settoptime"));
		a.setState(rs.getBoolean("articlestate"));
		a.setType(rs.getInt("type"));
		a.setState(rs.getBoolean("articlestate"));
		
		User au = new User();
		au.setActive(rs.getInt("auactive"));
		au.setCreatedate(rs.getString("aucreatedate"));
		au.setEmail(rs.getString("auemail"));
		au.setHeadpic(rs.getString("auheadpic"));
		au.setId(rs.getInt("auserid"));
		au.setLevel(rs.getInt("aulevel"));
		au.setRole(rs.getInt("aurole"));
		au.setScore(rs.getInt("auscore"));
		au.setState(rs.getBoolean("austate"));
		au.setUsername(rs.getString("ausername"));
		au.setUserpwd(rs.getString("auserpwd"));
		a.setUser(au);
		
		Blocks blocks = new Blocks();
		blocks.setContent(rs.getString("blockscontent"));
		blocks.setId(rs.getInt("blocksid"));
		blocks.setName(rs.getString("blocksname"));
		blocks.setPid(rs.getInt("pid"));
		a.setBlocks(blocks);
		
		collection.setArticle(a);
		return collection;
	}
}